<?php #title = Отслеживание пользователей;

/*
 * запрос для теста, чтобы пополнить таблицу легко
 *
 insert into sites_from_info values
   (18, 44, '2014-03-07', 4504, '2014-03-13', ''),
   (18, 45, '2014-03-07', 4505, '2014-03-13', ''),
   (18, 46, '2014-03-07', 4506, '2014-03-13', ''),
   (18, 47, '2014-03-07', 4507, '2014-03-13', ''),
   (18, 48, '2014-03-07', 4508, '2014-03-13', ''),
   (18, 49, '2014-03-07', 4509, '2014-03-13', ''),
   (18, 50, '2014-03-07', 4510, '2014-03-13', 'luchshiy-sayt.ru/luchshaya-stranica');
 * */

$i = 0;
$errors         = $one_s_orders = array();
$errors_html    = $success_html = $only_numbers = '';

if( isset($_FILES['orders_from_1c']) && $_FILES['orders_from_1c']['error'] == '' ){
    $file = fopen( $_FILES['orders_from_1c']['tmp_name'], 'r' );

    while( ( $data = fgetcsv( $file , '' , ';' , '"' ) ) !== FALSE )
    {
        if( $data[0] == '' )
            $errors[] = 'Нет значения с датой, строка '.$i;
        elseif( $data[1] == '' )
            $errors[] = 'Нет номера, строка '.$i;
        elseif( !is_numeric( $data[1] ) )
            $errors[] = 'Неправильное значение номера, строка '.$i;
        elseif( isset( $data[0] ) && isset( $data[1] ) && is_numeric($data[1]) ) {
            preg_match( '/(\d{2}.\d{2}.\d{4})/', $data[0], $date_only );

            $date = $date_only[0];

            /*echo $date.'<br/>';*/

            $date_array = explode( '.', $date );
            $date = $date_array[2].'-'.$date_array[1].'-'.$date_array[0];

            $one_s_orders[$data[1]] = $date;

            $only_numbers .= $data[1].', ';
        }
        $i++;
    }

    if( count($one_s_orders) ){

        $only_numbers = substr($only_numbers, 0, -2);

        $conn->query("select sfi.full_url, sfi.number, sfi.site_id, p.url, p.title, p.parents from sites_from_info sfi
                        join pages p on p.id = sfi.page_id
                            where number in({$only_numbers})");


        //number    date    page_title  page_url    visit_count site_name   site_name_desc  site_url
        if( $conn->rowCount() ){

            $insert_values = "insert into sites_from_orders(number, date, page_title, page_url, site_url, site_id) values";

            while( $row = $conn->fetch() ) {
                $full_url           = HOST_NAME.getProductBeforeUrl( $row->parents ).$row->url;
                $full_source_url    = ( isset($row->site_url) ? $row->site_url : '');
                $insert_values .= "(
                        {$row->number},
                        '{$one_s_orders[$row->number]}',
                        '{$row->title}',
                        '{$full_url}',
                        '{$full_source_url}',
                        '{$row->site_id}'
                ),";
            }

            $insert_values = substr($insert_values, 0, -1);

            /*echo $insert_values;*/

            $conn->query($insert_values);
            $conn->query("delete from sites_from_info where number in({$only_numbers})");
        }

        $success_html = 'Данные успешно обработаны';
    }

    /*echo '<pre>';
    print_r($one_s_orders);
    echo '</pre>';*/

    if(count($errors) > 0 ) {
        $errors_html .= '<ul>';
        foreach($errors as $err) {
            $errors_html .= '<li>'.$err.'</li>';
        }
        $errors_html .= '</ul>';
    }
}

/*print_r($_FILES);*/

$view->gText .= '
    <section class="page">
        <dl class="global-tabs">
            <dt class="global-tabs__title selected">Таблица сайтов источников</dt>
            <dd class="global-tabs__content selected">
                <div class="global-tabs__content__inner">
                    '.$load->block('mytable_editor', array('table_name'=>'sites_from', 'main_column'=>'id' , 'order_by'=>'order by visit_count desc')).'
                </div>
            </dd>


            <dt class="global-tabs__title">Загрузить файл из 1С</dt>
            <dd class="global-tabs__content">
                <div class="global-tabs__content__inner">
                    <form enctype="multipart/form-data" action="" method="POST">
                        <input type="file" name="orders_from_1c">
                        <input type="submit" name="orders_from_1c" value="Старт">
                    </form>

                    '.$errors_html.'
                    '.$success_html.'
                </div>
            </dd>

            <dt class="global-tabs__title">Собранная информация</dt>
            <dd class="global-tabs__content">
                <div class="global-tabs__content__inner">
                    '.$load->block('mytable_editor', array('table_name'=>'sites_from_orders', 'main_column'=>'id' , 'order_by'=>'order by date')).'
                </div>
            </dd>
        </dl>
    </section>
';

